insert
    overwrite
    table jms_dm.dm_customer_complaint_network_arr_pick_summary_dt
    partition
    (dt)
select t1.network_code                     --网点编码
     , t2.name             as network_name --网点名称
     , t2.center_code                      --中心编码
     , t2.center_name                      --中心名称
     , t2.fran_code                        --加盟商code
     , t2.fran_name                        --加盟商name
     , t2.agent_code                       --代理区code
     , t2.agent_name                       --代理区name
     , t2.virt_code                        --虚拟代理区code
     , t2.virt_name                        --虚拟代理区name
     , t1.tak_cnt                          --收件量
     , t1.arr_ctn                          --到件量
     , t1.date_type                        --类型1是天2是月
     , t2.zone_code        as district_code
     , t2.zone_name        as district_desc
     , t2.network_type
     , '{{ execution_date | cst_ds }}'        as date_time
     , t2.zone_id          as district_id
     , t2.provider_id
     , t2.provider_desc
     , t2.city_id
     , t2.city_desc
     , t2.area_code as area_code    --区域code
     , t2.area_name as area_name    --区域name
     , t2.leaf_area_id      as area_id      --区域name
     , '{{ execution_date | cst_ds }}'        as dt
from (
         select network_code
              , sum(tak_cnt) as tak_cnt
              , sum(arr_ctn) as arr_ctn
              , 1            as date_type
         from (--天维度
                  select pick_network_code as network_code
                       , count(1)          as tak_cnt
                       , 0                 as arr_ctn
                       , dt
                  from (--网点/集散  揽收量
                           select waybill_no
                                , pick_network_code
                                , dt
                           from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
                           where dt = '{{ execution_date | cst_ds }}'
                             and substr(input_time, 1, 10) = '{{ execution_date | cst_ds }}'
                       ) t
                           left join jms_dim.dim_network_whole_massage net
                                     on pick_network_code = net.code
                  where net.network_type in (5, 6)
                    and t.pick_network_code is not null
                  group by pick_network_code, dt

                  union all
                  select scan_site_code as network_code
                       , 0              as tak_cnt
                       , count(1)       as arr_ctn
                       , dt
                  from (--网点/集散 到件量
                           select scan_site_code
                                , waybill_no
                                , date(scan_time) as                                                                     dt
                                , row_number()
                                   over (partition by waybill_no,scan_site_code,date(scan_time) order by scan_time desc) rnk
                           from jms_dwd.dwd_tab_barscan_sitearrival_base_dt
                           where dt = '{{ execution_date | cst_ds }}'
                             and substr(scan_time, 1, 10) = '{{ execution_date | cst_ds }}'
                             and waybill_no like 'JT%'
                             and length(waybill_no) <= 15
                             and scan_site_code is not null
                             and network_type in (3, 4) --集散,网点
                       ) a
                  where rnk = 1
                  group by scan_site_code, dt
                  union all
                  --中心操作量 到件量
                  select scan_site_code
                       , 0             as tak_cnt
                       , sum(oper_cnt) as arr_ctn --操作量
                       , dt
                  from jms_dm.dm_center_transfer_summary_dt
                  where dt = '{{ execution_date | cst_ds }}'
                    and sum_flag = '自然日'
                  group by scan_site_code
                         , dt
              ) t
         group by network_code
         union all
         select network_code
              , sum(tak_cnt) as tak_cnt
              , sum(arr_ctn) as arr_ctn
              , 2            as date_type
         from (--月汇总
                  select pick_network_code as network_code
                       , count(1)          as tak_cnt
                       , 0                 as arr_ctn
                       , dt
                  from (--网点/集散  揽收量
                           select waybill_no
                                , pick_network_code
                                , dt
                           from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
                           where dt >= concat(substr('{{ execution_date | cst_ds }}', 1, 7), '-01')
                             and dt <= '{{ execution_date | cst_ds }}'
                             and substr(input_time, 1, 10) >=
                                 concat(substr('{{ execution_date | cst_ds }}', 1, 7), '-01')
                             and substr(input_time, 1, 10) <= '{{ execution_date | cst_ds }}'
                           -- and waybill_no like 'JT%' and length(waybill_no) <= 15
                       ) a
                           left join jms_dim.dim_network_whole_massage net
                                     on pick_network_code = net.code
                  where net.network_type in (5, 6)
                    and a.pick_network_code is not null
                  group by pick_network_code, dt

                  union all
                  select scan_site_code as network_code
                       , 0              as tak_cnt
                       , count(1)       as arr_ctn
                       , dt
                  from (--网点/集散  到件量
                           select scan_site_code
                                , waybill_no
                                , date(scan_time) as                                                                       dt
                                , row_number()
                                   over (partition by waybill_no, scan_site_code, date(scan_time) order by scan_time desc) rnk
                           from jms_dwd.dwd_tab_barscan_sitearrival_base_dt
                           where dt >= concat(substr('{{ execution_date | cst_ds }}', 1, 7), '-01')
                             and dt <= '{{ execution_date | cst_ds }}'
                             and substr(scan_time, 1, 10) >=
                                 concat(substr('{{ execution_date | cst_ds }}', 1, 7), '-01')
                             and substr(scan_time, 1, 10) <= '{{ execution_date | cst_ds }}'
                             and waybill_no like 'JT%'
                             and length(waybill_no) <= 15
                             and scan_site_code is not null
                             and network_type in (3, 4)
                       ) a
                  where a.rnk = 1
                  group by scan_site_code, dt

                  union all
                  select scan_site_code
                       , 0             as tak_cnt
                       , sum(oper_cnt) as arr_ctn --操作量
                       , dt
                  from jms_dm.dm_center_transfer_summary_dt
                  where dt >= concat(substr('{{ execution_date | cst_ds }}', 1, 7), '-01')
                    and dt <= '{{ execution_date | cst_ds }}'
                    and sum_flag = '自然日'
                  group by scan_site_code
                         , dt
              ) a
         group by network_code
     ) t1
         left join jms_dim.dim_network_whole_massage t2
                   on t1.network_code = t2.code
    distribute by dt
;
